#coding:utf-8
import os


import xlrd
from docx import *

import docx
import wc as wc
import xlrd
import xlwt
from docx import *
from xlutils.copy import copy

'''
#excel包安装
pip install xlrd
#docx 包安装
pip install docx
'''
#打开excel文件

# def open_excel(file= '1.xlsx'):
def open_excel(file= '北京区域组织机构树.xls'):
    try:
        data = xlrd.open_workbook(file)
        return data
    except Exception,e:
        print str(e)

#根据名称获取Excel表格中的数据   参数:file：Excel文件路径     colnameindex：表头列名所在行的索引  ，by_name：Sheet1名称
# def excel_table_byname(file= '1.xlsx', colnameindex=0, by_name=u'租户平台'):
def excel_table_byname(file= '1.xls', colnameindex=0, by_name=u'SQLResults'):
    data = open_excel(file) #打开excel文件
    table = data.sheet_by_name(by_name) #根据sheet名字来获取excel中的sheet
    nrows = table.nrows #行数
    colnames = table.row_values(colnameindex) #某一行数据
    list =[] #装读取结果的序列
    for rownum in range(1, nrows): #遍历每一行的内容
         row = table.row_values(rownum) #根据行号获取行
         if row: #如果行存在
             app = [] #一行的内容
             for i in range(len(colnames)): #一列列地读取行的内容
                app.append(row[i])
             list.append(app) #装载数据
    return list
'''
打开文档：
    doc = docx.Document(path)
添加段落：
doc.add_paragraph(u'第一段',style=None) 
获取所有段落：
p = docx.paragraphs
遍历：p=docx.paragraphs[0]  
      p.text
获取表格：
docx.tables
获取表格内容：
cell.text
'''
#写word
def writeWord(tables):
    document = Document()
    #一级标识
    i = 0
    #二级标识
    j = 0
    #三级标识
    n = 0
    #功能模块
    k = ''
    #子模块
    l = ''
    for row in tables:
        # paragraph = document.add_paragraph()
        # run = paragraph.add_run(row[6])
        # font = run.font
        # font.name = u'仿宋'
        title = ""
        if not row[3]=='':
            #一级
            i = i+1
            document.add_heading(str(i)+row[3], level=2)
            k = row[3]
            j = 0
            n = 0
        if not row[4]=='':
            j = j+1
            l = row[4]
            n = 0
        if not row[6]=='':
            n = n+1
        title = str(i)+str(".")+str(j)+str('.')+str(n)+row[6]
        print title
        document.add_heading(title, level=3)
        table = document.add_table(8, 2, style="Medium Grid 1 Accent 1")
        r0 = table.rows[0].cells
        r1 = table.rows[1].cells
        r2 = table.rows[2].cells
        r3 = table.rows[3].cells
        r4 = table.rows[4].cells
        r5 = table.rows[5].cells
        r6 = table.rows[6].cells
        r7 = table.rows[7].cells
        r1[0].text = u'系统名称'
        r1[1].text = row[1]

        r2[0].text = u'用例编号'
        r2[1].text = row[2]

        r3[0].text = u'功能模块'
        r3[1].text = row[3]
        if r3[1].text=='':
            r3[1].text = k

        r4[0].text = u'子模块'
        r4[1].text = row[4]
        if r4[1].text =='':
            r4[1].text =l

        r5[0].text = u'功能点'
        r5[1].text = row[6]

        r6[0].text = u'操作步骤'
        r6[1].text = row[8]

        r7[0].text = u'结果'
        r7[1].text = u'通过'

        # print row[3]
        # print row[4] #子模块
        # print row[5] #二级子模块
        # print row[6] #功能点
        # print row[7] #前置条件
        # print row[8] #操作步骤
        print '====================='
    return document

#业务逻辑层
def handler():
    #获取table所有数据
    tables = excel_table_byname()
    document = writeWord(tables)
    path = os.pardir + '/1.docx'
    document.save(path)

if __name__=="__main__":
    handler()
def test():
    path = os.pardir+"/1.docx"
    print path
    doc = docx.Document(path)
    p = doc.paragraphs
    for i in p:
        print i.text

def getLevelNo(list,code):
    for i in list:
        if i[14]==code and code!=0:
            i[17] = i[17]+"|"+str(i[15])+"|"+i[14]
            i[15] =code
            return i[14];

def save(data,i,j):
    rb = xlrd.open_workbook('1.xls')  # 打开weng.xls文件
    wb = copy(rb)  # 利用xlutils.copy下的copy函数复制
    ws = wb.get_sheet(0)  # 获取表单0
    ws.write(i, j, data)  # 改变（0,0）的值
    wb.save('1.xls')  # 保存文件


if __name__=="__main__":
    # handler()
    # test()
    list = excel_table_byname();
    aa = list

    # insert into `sys_department_info`(`depart_id`, `depart_name`, `depart_level`, `depart_type`, `parent_id`, `depart_level_no`, `company_id`, `source_no`, `create_time`, `create_id`, `update_time`, `update_id`) values(
    #     '01', '地产集团', '1', '1', '8094a7e931b44c12b8a2700d883533b5',
    #        '14c0ec89536246478b1ff5de7c755250|8094a7e931b44c12b8a2700d883533b5|b1b8c77c20644ccbb4e0c6ec071025a7',
    #        '14c0ec89536246478b1ff5de7c755250', '1', '1559180908348', '92931dac69264fc993c434ebc8adc279',
    #        '1559562363849', '92931dac69264fc993c434ebc8adc279');
    a_sql = "insert into `sys_department_info`(`depart_id`, `depart_name`, `depart_level`, `depart_type`, `parent_id`, `depart_level_no`, `company_id`, `source_no`, `create_time`, `create_id`, `update_time`, `update_id`,`crm_id`) values(";
    b_sql = ");"
    a = 0;
    b = 0;
    for i in list:
        a +=1;
        code =  str(int(i[0]));
        parentCode = str(int(i[2]));
        name = i[1];
        leavel = str(int(i[9]))
        timevalue = "1559180908348";
        serviceId = "92931dac69264fc993c434ebc8adc279";
        companyId = "14c0ec89536246478b1ff5de7c755250";
        level_no = "14c0ec89536246478b1ff5de7c755250|";
        llvo = str(i[17]);
        # if leavel=="4":
            # print("14c0ec89536246478b1ff5de7c755250|0|"  + str(int(i[0])) + "---" + str(a))
            # save("14c0ec89536246478b1ff5de7c755250|0|"  + str(int(i[0])),a,17)
            # for j in list:
            #     if int(i[2]) == int(j[0]):
            #         print(str(j[17])+"|"+str(int(i[0]))+"---"+str(a))
            #         save(str(j[17])+"|"+str(int(i[0])), a, 17)

        # if leavel=="4":
        #     b+=1;
        #     save(str(b),a,3)
            # print("--"+str(a)+"----"+str(b))









        print(
            a_sql+"'"+code+"','"
            +name+"','"
            +leavel+"','"
            +str(1)+"','"
            +parentCode+"','"
            +llvo+""+"','"
            +companyId+"','"
            +i[3]+"','"
            +timevalue+"','"
            +serviceId+"','"
            +timevalue+"','"
            +serviceId+"','"
            +str(i[14])+"'"
            +b_sql
        )
